/// <summary>
//                                                                      //
//  Developer   : Wicus Botha                                           //
//                                                                      //
//  Description :                                                       //
//                                                                      //
//  History     : WB - 2008-10-20 - Created                             //
//                                                                      //
/// </summary>

using System;
using System.Data;
using System.Data.Common;
using System.IO;
using System.IO.Compression;
using System.Runtime.Serialization.Formatters.Binary;
using log4net;

namespace IQ.DbBase
{
    public static class DbHelper
    {
        private static readonly ILog Log = LogManager.GetLogger("IQ.DbHelper");

        #region - Private variables -
        // Use Padlock to protect sql reader single access
        private static Object lockTable = new Object();
        private static Object lockReader = new Object();
        private static Object lockExec = new Object();
        //
        private static string connectionString;
        private static string connectionAddOn;
        //
        private const int defaultTimeOut = -1;
        #endregion

        #region - Connection & Settings -
        public static bool CanConnect;
        public static string Database;
        /// <summary>
        /// Global Connection Type
        /// This is needed to create the correct classes, currently SqlServer, Odbc & OleDB is caterred for
        /// </summary>
        public static ConnectionTypes ConnectionType = ConnectionTypes.SqlServer; // default
        /// <summary>
        /// Global Connection String
        /// Setting this connection string will create & set the Global connection
        /// </summary>
        public static void SetConnectionString(string globalConnectionString)
        {
            connectionString = globalConnectionString;
            DbConnection testConnection = GetConnection();
            try
            {
                testConnection.Open();
                Database = testConnection.Database;
                testConnection.Close();
                CanConnect = true;
            }
            catch (Exception e)
            {
                CanConnect = false;
                Database = "";
                Log.Error("Setting global connection: " + e.Message);
                throw e;
            }
        }
        public static void ConnectionAddOn(string value)
        {
            connectionAddOn = value;
        }
        public static DbConnection GetConnection()
        {
            return GetAConnection(ConnectionType, connectionString +";"+ connectionAddOn);
        }
        #endregion

        #region - Get DB types -
        /// <summary>
		/// Get a DB connection for the specific connection type
		/// </summary>
        public static DbConnection GetAConnection(ConnectionTypes connectionType, string connectionString)
        {
            switch (connectionType)
            {
                case ConnectionTypes.SqlServer: return new System.Data.SqlClient.SqlConnection(connectionString);
                case ConnectionTypes.Odbc: return new System.Data.Odbc.OdbcConnection(connectionString);
                case ConnectionTypes.OleDB: return new System.Data.OleDb.OleDbConnection(connectionString);
                //case null : return getConnection(globalConnectionType, connectionString);
                default: return null;
            }
        }

        /// <summary>
		/// Get a DB DataApapter for the specific connection type
		/// </summary>
        public static DbDataAdapter GetDataAdapter(ConnectionTypes conType)
        {
            switch (conType)
            {
                case ConnectionTypes.SqlServer: return new System.Data.SqlClient.SqlDataAdapter();
                case ConnectionTypes.Odbc: return new System.Data.Odbc.OdbcDataAdapter();
                case ConnectionTypes.OleDB: return new System.Data.OleDb.OleDbDataAdapter();
                default: return null;
            }
        }

        /// <summary>
        /// Get a new parameter with a specific size, only really relevant to string type params
        /// </summary>
        /// <param name="paramName">Parameter name</param>
        /// <param name="value">Parameter value</param>
        /// <param name="size">Parameter size, 0 will not set the size</param>
        /// <returns></returns>
        public static DbParameter GetCommandParameter(string paramName, object value, int size)
        {
            DbParameter param = null;
            switch (ConnectionType)
            {
                case ConnectionTypes.SqlServer: param = new System.Data.SqlClient.SqlParameter(paramName, value); break;
                case ConnectionTypes.Odbc: param = new System.Data.Odbc.OdbcParameter(paramName, value); break;
                case ConnectionTypes.OleDB: param = new System.Data.OleDb.OleDbParameter(paramName, value); break;
            }
            if (size > 0)
                param.Size = size;
            return param;
        }
        /// <summary>
        /// Get a new parameter for the current Connection type.  Will call overload method with size = 0.
        /// </summary>
        /// <param name="paramName">Parameter name</param>
        /// <param name="value">Parameter value</param>
        /// <returns></returns>
        public static DbParameter GetCommandParameter(string paramName, object value)
        {
            return GetCommandParameter(paramName, value, 0);
        }
        #endregion

        #region - DataSet -
        public static DataSet Load_DataSet(string sql)
        {
            DbDataAdapter dapDB = GetDataAdapter(ConnectionType);
            DataSet ds = new DataSet();
            ds.DataSetName = DateTime.Now.Millisecond.ToString();
            DbConnection connection = GetConnection();
            DbCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            dapDB.SelectCommand = cmd;
            try
            {
                connection.Open();
                dapDB.Fill(ds);
                return ds;
            }
            catch (Exception e)
            {
                Log.Error("Data Table load: " + e.Message + "; SQL=" + sql);
                return null;
            }
            finally
            {
                connection.Close();
            }
        }
        public static void SaveDataSetToFile(DataSet ds, string fileName)
        {
            //
            FileStream fs = new FileStream(fileName, FileMode.Create);
            GZipStream gzOut = new GZipStream(fs, CompressionMode.Compress);
            //
            try
            {
                ds.RemotingFormat = SerializationFormat.Binary;
                BinaryFormatter bin = new BinaryFormatter();
                bin.Serialize(gzOut, ds);
            }
            finally
            {
                gzOut.Close();
                fs.Close();
            }
        }
        public static DataSet LoadDataSetFromFile(string fileName)
        {
            if (File.Exists(fileName))
            {
                DataSet ds = new DataSet();
                ds.RemotingFormat = SerializationFormat.Binary;
                FileStream fs = new FileStream(fileName, FileMode.Open);
                GZipStream gzOut = new GZipStream(fs, CompressionMode.Decompress);
                try
                {
                    BinaryFormatter bin = new BinaryFormatter();
                    ds = (DataSet)bin.Deserialize(gzOut);
                }
                catch
                {
                    ds = null;
                }
                finally
                {
                    gzOut.Close();
                    fs.Close();
                }
                return ds;
            }
            else
                return null;
        }

        #endregion

        #region - Data Tables -
        public static void AddDataColumn(DataTable table, string colName, string colType, bool colNull, object colDefault)
        {
            DataColumn column = new DataColumn();
            column.DataType = System.Type.GetType(colType);
            column.ColumnName = colName;
            column.AllowDBNull = colNull;
            column.DefaultValue = colDefault;
            try
            {
                table.Columns.Add(column);
            }
            catch (Exception e)
            {
                Log.Error("Adding data column: " + e.Message);
                throw e;
            }
        }
        public static void AddKeyToTable(DataTable table, string colNames)
        {
            string[] colArray = colNames.Split(';');
            DataColumn[] keys = new DataColumn[colArray.Length];
            for (int i = 0; i < colArray.Length; i++)
            {
                keys[i] = table.Columns[colArray[i]];
            }
            table.PrimaryKey = keys;
        }

        /// <summary>
        /// Get and Error DataTable
        /// </summary>
        public static DataTable GetErrorTable(string error)
        {
            DataTable table = new DataTable("Error");
            DbHelper.AddDataColumn(table, "Error", "System.String", true, "");
            DataRow row = table.NewRow();
            row["Error"] = "Could not run sql, error = " + error;
            table.Rows.Add(row);
            return table;
        }
        /// <summary>
        /// Load a data table with a parameterized query, improves caching on Sql server
        /// </summary>
        /// <param name="sqlType">Text / Store procedure</param>
        /// <param name="sql">sql query to run</param>
        /// <param name="sqlParams">list of parameters, use DbHelper.GetCommandParameter(), to get parameters</param>
        /// <returns>Loaded DataTable</returns>
        public static DataTable LoadTable(CommandType sqlType, string sql, params DbParameter[] sqlParams)
        {
            DataTable dt = new DataTable();
            dt.TableName = DateTime.Now.Millisecond.ToString();
            DbConnection connection = GetConnection();
            DbCommand cmd = connection.CreateCommand();
            cmd.CommandType = sqlType;
            cmd.CommandText = sql;
            for (int i = 0; i < sqlParams.Length; ++i)
            {
                cmd.Parameters.Add(sqlParams[i]);
            }
            DbDataAdapter dap = GetDataAdapter(ConnectionType);
            dap.SelectCommand = cmd;
            try
            {
                connection.Open();
                dap.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                Log.Error("Data Table load: " + e.Message + "; SQL=" + sql);
                //return GetErrorTable(e.Message);
                throw e;
            }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        ///  Load a data table with a parameterized query, improves caching on Sql server, overload on LoadTable,
        ///  this will send the command type thru as Text
        /// </summary>
        /// <param name="sql">sql query to run</param>
        /// <param name="sqlParams">list of parameters, use DbHelper.GetCommandParameter(), to get parameters</param>
        /// <returns>Loaded DataTable</returns>
        public static DataTable LoadTable(string sql, params DbParameter[] sqlParams)
        {
            return LoadTable(CommandType.Text, sql, sqlParams);
        }

        public static DataTable LoadTable(string connection, string sql)
        {
            DataTable dt = new DataTable();
            dt.TableName = DateTime.Now.Millisecond.ToString();
            DbConnection tmpCon = GetAConnection(ConnectionType, connection);
            DbCommand cmd = tmpCon.CreateCommand();
            cmd.CommandText = sql;
            DbDataAdapter dap = GetDataAdapter(ConnectionType);
            dap.SelectCommand = cmd;
            try
            {
                tmpCon.Open();
                dap.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                Log.Error("Data Table load: " + e.Message + "; SQL=" + sql);
                //return GetErrorTable(e.Message);
                throw e;
            }
            finally
            {
                tmpCon.Close();
            }
        }
        public static DataTable LoadTable(string sql, DbConnection connection)
        {
            DataTable dt = new DataTable();
            dt.TableName = DateTime.Now.Millisecond.ToString();
            DbCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            DbDataAdapter dap = GetDataAdapter(ConnectionType);
            dap.SelectCommand = cmd;
            try
            {
                connection.Open();
                dap.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                Log.Error("Data Table load: " + e.Message + "; SQL=" + sql);
                //return GetErrorTable(e.Message);
                throw e;
            }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// Load DataTable. 
        /// Use Global ConnectionType & Global Connection
        /// </summary>
        public static DataTable Load_Table(string sql)
        {
            DataTable dt = new DataTable();
            dt.TableName = DateTime.Now.Millisecond.ToString();
            DbConnection connection = GetConnection();
            DbCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            DbDataAdapter dap = GetDataAdapter(ConnectionType);
            dap.SelectCommand = cmd;
            try
            {
                connection.Open();
                dap.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                Log.Error("Data Table load: " + e.Message + "; SQL=" + sql);
                //return GetErrorTable(e.Message);
                throw e;
            }
            finally            
            {
                connection.Close();
            }
        }
        /// <summary>
        /// Load DataTable.
        /// Use Global ConnectionType & Global Connection
        /// Changes timeout duration
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="timeOut"></param>
        /// <returns></returns>
        public static DataTable Load_Table(string sql, int timeOut)
        {
            DataTable dt = new DataTable();
            dt.TableName = DateTime.Now.Millisecond.ToString();
            DbConnection connection = GetConnection();
            DbCommand cmd = connection.CreateCommand();
            cmd.CommandTimeout = timeOut;
            cmd.CommandText = sql;
            DbDataAdapter dap = GetDataAdapter(ConnectionType);
            dap.SelectCommand = cmd;
            try
            {
                connection.Open();
                dap.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                Log.Error("Data Table load: " + e.Message + "; SQL=" + sql);
                //return GetErrorTable(e.Message);
                throw e;
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Load DataTable from your XML string. 
        /// </summary>
        public static DataTable Load_TableXml(string xml)
        {
            DataTable dataHolder = new DataTable();
            try
            {
                dataHolder.ReadXml(xml);
                return dataHolder;
            }
            catch (Exception e)
            {
                // #Log "Error in load: " + e.Message;
                // return GetErrorTable(e.Message);
                throw e;
            }
        }
        
        /// <summary>
        /// Save DataTable to XML file.
        /// </summary>
        public static void Save_TableXml(DataTable table, string fileName)
        {
            table.WriteXml(fileName, XmlWriteMode.WriteSchema);
        }

        #endregion

        #region - Prepare DbCommand
        /// <summary>
        /// Creates, Prepare and Returns a Command
        /// </summary>
        public static DbCommand PrepareCommand(DbConnection connection, string sql, CommandType cmdType,
            DbTransaction transaction, DbParameter[] cmdParams)
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                DbCommand cmd = connection.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = cmdType;
                // Add Transaction
                if (transaction != null)
                    cmd.Transaction = transaction;
                // Add Parameters
                if (cmdParams != null)
                {
                    foreach (DbParameter param in cmdParams)
                        if (param != null)
                            cmd.Parameters.Add(param);
                }
                return cmd;
            }
            catch (Exception e)
            {
                Log.Error("Perpare command: " + e.Message + "; SQL=" + sql);
                throw e;
            }
        }
        #endregion

        #region - Data Reader -
        public static DbDataReader Load_Reader(DbConnection connection,
            string sql, DbTransaction transaction, CommandType cmdType, CommandBehavior behavior,
            params DbParameter[] cmdParams)
        {
            lock (lockReader)
            {
                try
                {
                    DbCommand cmd = PrepareCommand(connection, sql, cmdType, transaction, cmdParams);
                    DbDataReader dataReader = null;
                    dataReader = cmd.ExecuteReader(behavior);
                    // cmd.Parameters.Clear(); ??
                    return dataReader;
                }
                catch (Exception e)
                {
                    Log.Error("Data reader load" + e.Message + "; SQL=" + sql);
                    connection.Close();
                    throw e;
                }
            }
        }
        //
        public static DbDataReader Load_Reader(ConnectionTypes conType, string connectionStr,
            string sql, DbTransaction transaction, CommandType cmdType,
            params DbParameter[] cmdParams)
        {
            DbConnection connectionDB = null;
            CommandBehavior behavior = CommandBehavior.Default;
            if (transaction == null)
            {   // Creates a new connection and we want it to close when the reader closes
                connectionDB = GetAConnection(conType, connectionStr);
                behavior = CommandBehavior.CloseConnection;
            }
            else
                connectionDB = transaction.Connection;
            //
            try
            {
                return Load_Reader(connectionDB, sql, transaction, cmdType, behavior, cmdParams);
            }
            catch (Exception e)
            {
                connectionDB.Close();
                throw e;
            }
        }

        public static DbDataReader Load_Reader(string sql, CommandType cmdType)
        {
            DbConnection connection = GetConnection();
            connection.Open();
            DbCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = cmdType;
            try
            {
                DbDataReader dataReader = null;
                dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dataReader;
            }
            catch (Exception e)
            {
                connection.Close();
                Log.Error("Data reader load" + e.Message + "; SQL=" + sql);
                throw e;
            }
            finally
            {
                //connection.Close(); // Cannot close connection becuase then reader is gone
            }
        }        
        // Load Reader CommandType = Text
        public static DbDataReader Load_Reader(string sql)
        {
            try
            {
                return Load_Reader(sql, CommandType.Text);
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        // Load Store procedure
        public static DbDataReader Load_ReaderSP(string connectionStr, string sql)
        {
            try
            {
                return Load_Reader(sql, CommandType.StoredProcedure);
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        #endregion

        #region - Execute SQL -
        public static object ExecuteSql(ConnectionTypes conType, string connectionStr,
            string sql, DbTransaction transaction, CommandType cmdType,
            CmdExecuteType execType, params DbParameter[] cmdParams)
        {
            DbConnection connection = null;
            if (transaction == null)
                connection = GetAConnection(conType, connectionStr);
            else
                connection = transaction.Connection;
            //
            DbCommand cmd = PrepareCommand(connection, sql, cmdType, transaction, cmdParams);
            try
            {
                if (execType == CmdExecuteType.NonQuery)
                {
                    object res = cmd.ExecuteNonQuery();
                    return res;
                }
                else
                {
                    return cmd.ExecuteScalar();
                }
            }
            catch (Exception e)
            {
                Log.Error("Execute SQL: " + e.Message + "; SQL=" + sql);
                throw e;
                //return e.Message;
            }
            finally
            {
                connection.Close();
            }
        }
        public static object ExecuteSql(string sql, CmdExecuteType execType, DbConnection connection, DbTransaction transaction,
            bool closeConnection, int timeOut = defaultTimeOut)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
            DbCommand cmd = PrepareCommand(connection, sql, CommandType.Text, transaction, null);
            if (timeOut != defaultTimeOut)
                cmd.CommandTimeout = timeOut;
            cmd.CommandText = sql;
            try
            {
                object res = null;
                if (execType == CmdExecuteType.NonQuery)
                    res = cmd.ExecuteNonQuery();
                else
                    res = cmd.ExecuteScalar();
                return res;
            }
            catch (Exception e)
            {
                Log.Error("Execute SQL: " + e.Message + "; SQL=" + sql);
                throw e;
                //return e.Message;
            }
            finally
            {
                if (closeConnection)
                    connection.Close();
            }
        }
        public static object ExecuteSql(string connection, string sql, CmdExecuteType execType)
        {
            DbConnection tmpCon = GetAConnection(ConnectionType, connection);
            tmpCon.Open();
            DbCommand cmd = tmpCon.CreateCommand();
            cmd.CommandText = sql;
            try
            {
                object res = null;
                if (execType == CmdExecuteType.NonQuery)
                    res = cmd.ExecuteNonQuery();
                else
                    res = cmd.ExecuteScalar();
                return res;
            }
            catch (Exception e)
            {
                Log.Error("Execute SQL: " + e.Message + "; SQL=" + sql);
                throw e;
                //return e.Message;
            }
            finally
            {
                tmpCon.Close();
            }
        }
        public static object ExecuteSql(string sql, DbConnection connection, CmdExecuteType execType, int timeOut = defaultTimeOut)
        {
            connection.Open();
            DbCommand cmd = connection.CreateCommand();
            if (timeOut != defaultTimeOut)
                cmd.CommandTimeout = timeOut;
            cmd.CommandText = sql;
            try
            {
                object res = null;
                if (execType == CmdExecuteType.NonQuery)
                    res = cmd.ExecuteNonQuery();
                else
                    res = cmd.ExecuteScalar();
                return res;
            }
            catch (Exception e)
            {
                Log.Error("Execute SQL: " + e.Message + "; SQL=" + sql);
                throw e;
                //return e.Message;
            }
            finally
            {
                connection.Close();
            }
        }        
        public static object ExecuteSql(string sql, CmdExecuteType execType, int timeOut = defaultTimeOut)
        {
            DbConnection connection = GetConnection();
            connection.Open();
            DbCommand cmd = connection.CreateCommand();
            if (timeOut != defaultTimeOut)
                cmd.CommandTimeout = timeOut;
            cmd.CommandText = sql;
            try
            {
                object res = null;
                if (execType == CmdExecuteType.NonQuery)
                    res = cmd.ExecuteNonQuery();
                else
                    res = cmd.ExecuteScalar();
                return res;
            }
            catch (Exception e)
            {
                Log.Error("Execute SQL: " + e.Message + "; SQL=" + sql);
                throw e;
                //return e.Message;
            }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// Execute sql with a time out in seconds
        /// </summary>
        /// <param name="sql">sql query to execute</param>
        /// <param name="timeOut">Time out - seconds, 0 = wait until command completed</param>
        /// <returns></returns>
        public static object ExecuteSql(string sql, int timeOut)
        {
            return ExecuteSql(sql, CmdExecuteType.Scalar, timeOut);
        }
        //
        public static object ExecuteSql(string sql)
        {
            return ExecuteSql(sql, CmdExecuteType.Scalar);
        }
        // Returns String
        public static String ExecuteSql_rStr(string sql)
        {
            try
            {
                return Convert.ToString(ExecuteSql(sql));
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        public static String ExecuteSql_rStrE(string sql)
        {
            try
            {
                return Convert.ToString(ExecuteSql(sql));
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }
        public static String ExecuteSql_rStrE(string sql, string errorValue)
        {
            try
            {
                return Convert.ToString(ExecuteSql(sql));
            }
            catch 
            {
                return errorValue;
            }
        }
        // Returns Integer
        public static Int32 ExecuteSql_rInt(string sql)
        {
            try
            {
                return Convert.ToInt32(ExecuteSql(sql));
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        public static Int32 ExecuteSql_rInt(string sql, int errorValue)
        {
            try
            {
                return Convert.ToInt32(ExecuteSql(sql));
            }
            catch
            {
                return errorValue;
            }
        }
        public static Int32 ExecuteSql_rInt(string sql, int errorValue, int timeOut = defaultTimeOut)
        {
            try
            {
                return Convert.ToInt32(ExecuteSql(sql, timeOut));
            }
            catch
            {
                return errorValue;
            }
        }
        // Returns Decimal
        public static Decimal ExecuteSql_rDec(string sql)
        {
            try
            {
                return Convert.ToDecimal(ExecuteSql(sql));
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        public static Decimal ExecuteSql_rDec(string sql, Decimal errorValue)
        {
            try
            {
                return Convert.ToDecimal(ExecuteSql(sql));
            }
            catch
            {
                return errorValue;
            }
        }
        // Returns Double
        public static Double ExecuteSql_rDbl(string sql)
        {
            try
            {
                return Convert.ToDouble(ExecuteSql(sql));
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        public static Double ExecuteSql_rDbl(string sql, Double errorValue)
        {
            try
            {
                return Convert.ToDouble(ExecuteSql(sql));
            }
            catch
            {
                return errorValue;
            }
        }
        // Returns Single
        public static Single ExecuteSql_rSgl(string sql)
        {
            try
            {
                return Convert.ToSingle(ExecuteSql(sql));
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        public static Single ExecuteSql_rSgl(string sql, Single errorValue)
        {
            try
            {
                return Convert.ToSingle(ExecuteSql(sql));
            }
            catch
            {
                return errorValue;
            }
        }
        // Returns DateTime
        public static DateTime ExecuteSql_rDT(string sql)
        {
            try
            {
                return Convert.ToDateTime(ExecuteSql(sql));
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        public static DateTime ExecuteSql_rDT(string sql, DateTime errorValue)
        {
            try
            {
                return Convert.ToDateTime(ExecuteSql(sql));
            }
            catch
            {
                return errorValue;
            }
        }
        // Returns Rows Effected
        public static int ExecuteSql_rRowsEffected(string sql)
        {
            return (Int32)ExecuteSql(sql, CmdExecuteType.NonQuery);
        }
        #endregion       
       
        public static IDataRecord GetDataRecord(string connectionStr, string sql)
        {
            DbDataReader dr = Load_Reader(sql, CommandType.Text);
            if (dr != null && dr.HasRows)
            {
                dr.Read();
                return dr;
            }
            else
                return null;
        }
    }
}
